#ifndef __M_DB_H__
#define __M_DB_H__
#include "util.hpp"
#include <mutex>
#include <cassert>
class user_table {  
private:
    MYSQL* _mysql;       // mysql句柄
    std::mutex _mutex;   // 保护数据库访问操作

public:
    user_table(const std::string& host, const std::string& username, 
               const std::string& password, const std::string& dbname, 
               uint16_t port = 3306) {
        
        _mysql = mysql_util::mysql_create(host, username, password, dbname, port);
        assert(_mysql != nullptr);  
    }

    ~user_table() {
        if (_mysql != nullptr) {
            mysql_util::mysql_destroy(_mysql);
            _mysql = nullptr;
        }
    }

    // 注册增加用户
    bool insert(Json::Value& user) {
        // 增加SQL注入防护，使用占位符思想
        #define INSERT_USER "insert into user values(null,'%s',password('%s'),1000,0,0);"
        char sql[4096] = {0};
        // 检查JSON字段是否存在
        if (!user.isMember("username") || !user.isMember("password")) {
            ELOG("missing username or password in user data\n");
            return false;
        }
        // 使用snprintf更安全
        snprintf(sql, sizeof(sql)-1, INSERT_USER,
                 user["username"].asCString(),
                 user["password"].asCString());
        
        std::unique_lock<std::mutex> lock(_mutex);  // 增加互斥锁保护
        bool ret = mysql_util::mysql_exec(_mysql, sql);
        if (!ret) {  
            ELOG("insert user info failed: %s\n", mysql_error(_mysql));
            return false;
        }
        return true;
    }

    // 登录用户
   	bool login(Json::Value &user) {  // 登录用户
    // 用户名+密码为过滤条件，查询到数据表示用户名密码一致，反之错误
#define LOGIN_USER "select id,score,total_count,win_count from user where username='%s' and password=password('%s');"
    char sql[4096] = {0};
    
    // 检查JSON中是否包含必要的用户名和密码
    if (!user.isMember("username") || !user.isMember("password")) {
        ELOG("login: missing username or password\n");
        return false;
    }
    
    // 安全拼接SQL（用snprintf防止缓冲区溢出）
    const char* username = user["username"].asCString();
    snprintf(sql, sizeof(sql)-1, LOGIN_USER, 
             username, 
             user["password"].asCString());
    
    MYSQL_RES* res = NULL;  
    {
        std::unique_lock<std::mutex> lock(_mutex);
        
        // 执行SQL查询
        bool ret = mysql_util::mysql_exec(_mysql, sql);
        // 修正判断逻辑：ret为false才是执行失败
        if (!ret) {  
            ELOG("login: SQL execute failed for user %s: %s\n", 
                 username, mysql_error(_mysql));
            return false;
        }
        
        // 获取查询结果集
        res = mysql_store_result(_mysql);
        if (res == NULL) {
            ELOG("login: no result for user %s: %s\n", 
                 username, mysql_error(_mysql));
            return false;
        }
        
        // 检查结果行数（必须为1行才是有效用户）
        int row_num = mysql_num_rows(res);
        // 修正变量名：row→row_num
        if (row_num != 1) {  
            ELOG("login: user %s not found or duplicate (rows: %d)\n", 
                 username, row_num);
            mysql_free_result(res);  // 及时释放资源
            return false;
        }
    }
    
    // 提取行数据
    MYSQL_ROW row = mysql_fetch_row(res);
    if (row == NULL) {
        ELOG("login: fetch row failed for user %s\n", username);
        mysql_free_result(res);
        return false;
    }
    
    try {
        // 转换数据并存入JSON（用stoull更安全，处理无符号64位）
        user["id"] = (Json::UInt64)std::stoull(row[0]);
        user["score"] = (Json::UInt64)std::stoull(row[1]);
        user["total_count"] = std::stoi(row[2]);
        user["win_count"] = std::stoi(row[3]);
    } catch (const std::exception& e) {
        // 捕获转换异常（如数据格式错误）
        ELOG("login: data convert failed for user %s: %s\n", username, e.what());
        mysql_free_result(res);
        return false;
    }
    
    // 释放结果集
    mysql_free_result(res);
    DLOG("login: user %s success\n", username);
    return true;
}
    // 通过用户名获取信息
    bool select_by_name(const std::string& name, Json::Value& user) {
        #define USER_BY_NAME "select id,score,total_count,win_count from user where username='%s';"
        char sql[4096] = {0};
        snprintf(sql, sizeof(sql)-1, USER_BY_NAME, name.c_str());
        
        MYSQL_RES* res = nullptr;
        {
            std::unique_lock<std::mutex> lock(_mutex);  
            bool ret = mysql_util::mysql_exec(_mysql, sql);
            if (!ret) {
                ELOG("select by name failed: %s\n", mysql_error(_mysql));
                return false;
            }
            res = mysql_store_result(_mysql);
            if (res == nullptr) {
                ELOG("mysql_store_result failed: %s\n", mysql_error(_mysql));
                return false;
            }
            int row_num = mysql_num_rows(res);
            if (row_num != 1) {  
                ELOG("user by name not unique or not found, rows: %d\n", row_num);
                mysql_free_result(res);
                return false;
            }
        }
        
        MYSQL_ROW row = mysql_fetch_row(res);
        if (row != nullptr) {
            user["id"] = (Json::UInt64)std::stoull(row[0]);
            user["username"] = name;
            user["score"] = (Json::UInt64)std::stoull(row[1]);
            user["total_count"] = std::stoi(row[2]);
            user["win_count"] = std::stoi(row[3]);
        } else {
            ELOG("fetch row failed\n");
            mysql_free_result(res);
            return false;
        }
        
        mysql_free_result(res);
        return true;
    }

    // 通过id获取信息
    bool select_by_id(uint64_t id, Json::Value& user) {
        #define USER_BY_ID "select username,score,total_count,win_count from user where id=%llu;"  
        char sql[4096] = {0};
        snprintf(sql, sizeof(sql)-1, USER_BY_ID, id);  
        
        MYSQL_RES* res = nullptr;
        {
            std::unique_lock<std::mutex> lock(_mutex);  
            bool ret = mysql_util::mysql_exec(_mysql, sql);
            if (!ret) {
                ELOG("select by id failed: %s\n", mysql_error(_mysql));
                return false;
            }
            res = mysql_store_result(_mysql);
            if (res == nullptr) {
                ELOG("mysql_store_result failed: %s\n", mysql_error(_mysql));
                return false;
            }
            int row_num = mysql_num_rows(res);
            if (row_num != 1) {  
                ELOG("user by id not unique or not found, rows: %d\n", row_num);
                mysql_free_result(res);
                return false;
            }
        }
        
        MYSQL_ROW row = mysql_fetch_row(res);
        if (row != nullptr) {
            user["id"] = (Json::UInt64)id;
            user["username"] = row[0];
            user["score"] = (Json::UInt64)std::stoull(row[1]);  
            user["total_count"] = std::stoi(row[2]);
            user["win_count"] = std::stoi(row[3]);
        } else {
            ELOG("fetch row failed\n");
            mysql_free_result(res);
            return false;
        }
        
        mysql_free_result(res);
        return true;
    }
		bool win(uint64_t id) {  // 胜利：增加30分、比赛场次和胜利场次
    // 修正占位符：%d → %llu（匹配uint64_t类型）
#define USER_WIN "update user set score=score+30, total_count=total_count+1, win_count=win_count+1 where id=%llu;"
    char sql[4096] = {0};
    
    // 1. 安全拼接SQL（用snprintf防止缓冲区溢出）
    int len = snprintf(sql, sizeof(sql)-1, USER_WIN, id);
    if (len < 0 || len >= (int)sizeof(sql)-1) {
        ELOG("win: SQL buffer overflow for id=%llu\n", id);
        return false;
    }
    
    // 2. 加锁保护，确保线程安全
    std::unique_lock<std::mutex> lock(_mutex);
    
    // 3. 执行SQL并修正判断逻辑
    bool ret = mysql_util::mysql_exec(_mysql, sql);
    if (!ret) {  // 执行失败（ret为false时）
        ELOG("win: update failed! id=%llu, error=%s, SQL=%s\n",
             id, mysql_error(_mysql), sql);  // 补充具体错误信息
        return false;
    }
    
    DLOG("win: update success! id=%llu\n", id);
    return true;
}

bool lose(uint64_t id) {  // 失败：减少30分、增加比赛场次
    // 修正占位符：%d → %llu（匹配uint64_t类型）
#define USER_LOSE "update user set score=score-30, total_count=total_count+1 where id=%llu;"
    char sql[4096] = {0};
    
    // 1. 安全拼接SQL
    int len = snprintf(sql, sizeof(sql)-1, USER_LOSE, id);
    if (len < 0 || len >= (int)sizeof(sql)-1) {
        ELOG("lose: SQL buffer overflow for id=%llu\n", id);
        return false;
    }
    
    // 2. 加锁保护
    std::unique_lock<std::mutex> lock(_mutex);
    
    // 3. 执行SQL并修正判断逻辑
    bool ret = mysql_util::mysql_exec(_mysql, sql);
    if (!ret) {  // 执行失败（ret为false时）
        ELOG("lose: update failed! id=%llu, error=%s, SQL=%s\n",
             id, mysql_error(_mysql), sql);
        return false;
    }
    
    DLOG("lose: update success! id=%llu\n", id);
    return true;
}

};
#endif
